<?php
/**
 * 统计分析模块
 */
namespace app\controllers;
use app\components\EmailMarket;
use Yii;
use yii\filters\AccessControl;
use yii\mongodb\Query;
use yii\web\Controller;
use yii\data\Pagination;
use app\models\EmailSystemEmail;
use app\models\MailTemplateModel;
use yii\helpers\Html;
use app\components\TemplateMarket;

class StatisticalAnalysisController extends \app\base\Controller
{
    /**
     * 邮件转化统计列表
     */
    public function actionIndex()
    {
        $data = [];
        $siteInfo = [];
        
        $defaultPageSize = 20;
        $mothod = strtolower(Yii::$app->request->getMethod());
        
        // 查询网站相关的数据
        $formData = Yii::$app->request->$mothod();
        $site = trim($formData['site']);
        
       // 当前用户所有有权限的站点
        $siteInfo_tmp = Yii::$app->authority->getSysEmailAccessSite();
        
        if (isset($siteInfo_tmp) && is_array($siteInfo_tmp)) {
            foreach ($siteInfo_tmp as $siteInfo_tmp_k => $siteInfo_tmp_v) {
                $siteInfo[$siteInfo_tmp_v['site_id']] = $siteInfo_tmp_v['name'];
            }
        }
        
        // 默认的时间数据
        if (!empty($formData['reservation_time'])){
            $reservationTimeArr = explode(' - ',$formData['reservation_time']);
            $formData['startTime'] = trim($reservationTimeArr[0]);
            $formData['endTime'] = trim($reservationTimeArr[1]);
        }
        // 当前时间 
        $curTime = \yii::$app->bjTime->time();
        $timeYearMonthStart = \yii::$app->bjTime->date('Y-m-d',$curTime-30*24*60*60);
        $timeYearMonthCurrent = \yii::$app->bjTime->date('Y-m-d',$curTime);
        $formData['startTime'] = !empty($formData['startTime']) ? $formData['startTime'] : $timeYearMonthStart;
        $formData['endTime'] = !empty($formData['endTime']) ? $formData['endTime'] : $timeYearMonthCurrent;
        
        // 当前用户所有有权限的站点结束
        $formData['siteInfo'] = $siteInfo;
        
        // 当前搜索哪个网站的用户信息
        $formData['site_id'] = isset($formData['site_id']) ? intval($formData['site_id']) : array_keys($siteInfo)[0];
        
        $formData['templateType'][0] = '全部';
        
        $reg_source= ['ALL'=> '全部'];
        if ($formData['site_id']) {
            $flag = Yii::$app->authority->judgeSysEmailHaveSite($formData['site_id']);
            if (!$flag) {
                 $this->sysMsg('警告', '你的账号没有权限!',$params = [ 'type' => 'error', 'links' => [ ['name'=>'返回上一页' , 'url'=>'javascript:history.go(-1)'] ] ]);
            }
            // 邮件类型
            $templateModel = new MailTemplateModel;
            $templateTypes = $templateModel->getTemplateType($formData['site_id']);
            foreach($templateTypes as $templateType) {
                $formData['templateType'][$templateType['id']] = $templateType['sys_template_type_description'];
            }
            
            $reg_source = TemplateMarket::regSourceList($formData['site_id']);
        }
        
        $formData['template_type_id'] = isset($formData['template_type_id']) ? intval($formData['template_type_id']) : 0;
        $formData['reg_source'] = isset($formData['reg_source']) ? trim($formData['reg_source']) : 'ALL';
        
        // 分页参数相关
        $page = Yii::$app->request->$mothod('page') ? Yii::$app->request->$mothod('page') : 1;
        $pageSize = Yii::$app->request->$mothod('pagesize') ? Yii::$app->request->$mothod('pagesize') : $defaultPageSize;
        $formData['page'] = $page;
        $formData['pageSize'] = $pageSize;
        
        // 导出数据
        if(isset($formData['downLoad']) && $formData['downLoad']) {
                $data = $this->downLoadStatisticsData($formData);
                if($data['code'] != 0) {
                    $this->sysMsg('警告',$data['msg'],$params = [ 'type' => 'error', 'links' => [ ['name'=>'返回上一页' , 'url'=>'javascript:history.go(-1)'] ] ]);
                }
                return;
        } else {
        
        
                $searchCondition = '';
                if($formData['site_id']) {
                    $searchCondition .= '&site_id='.$formData['site_id'];
                }
                if(isset($formData['reservation_time'])) {
                    $searchCondition .= '&reservation_time='. $formData['reservation_time'];
                }
                if(isset($formData['template_type_id'])) {
                    $searchCondition .= '&template_type_id='. $formData['template_type_id'];
                }
                if(isset($formData['reg_source']) && $formData['reg_source'] != 'ALL') {
                    $searchCondition .= '&amp;reg_source='. $formData['reg_source'];
                }

                // 送达率排序连接
                $orderLink['sendSuccessOrder'] = '/statistical-analysis/index?site=sys_email&order_name=sendSuccessOrder&order=1'.$searchCondition;
                if(isset($formData['order_name']) && $formData['order_name'] == 'sendSuccessOrder' && $formData['order'] == 1) {
                    $orderLink['sendSuccessOrder'] = '/statistical-analysis/index?site=sys_email&order_name=sendSuccessOrder&order=2'.$searchCondition;
                }
                // 送达率排序连接
                $orderLink['sendTimeLongOrder'] = '/statistical-analysis/index?site=sys_email&order_name=sendTimeLongOrder&order=1'.$searchCondition;
                if(isset($formData['order_name']) && $formData['order_name'] == 'sendTimeLongOrder' && $formData['order'] == 1) {
                    $orderLink['sendTimeLongOrder'] = '/statistical-analysis/index?site=sys_email&order_name=sendTimeLongOrder&order=2'.$searchCondition;
                }
                // 打开率排序连接
                $orderLink['openOrder'] = '/statistical-analysis/index?site=sys_email&order_name=openOrder&order=1'.$searchCondition;
                if(isset($formData['order_name']) && $formData['order_name'] == 'openOrder' && $formData['order'] == 1) {
                    $orderLink['openOrder'] = '/statistical-analysis/index?site=sys_email&order_name=openOrder&order=2'.$searchCondition;
                }
                // 弹回率排序连接
                $orderLink['bounceOrder'] = '/statistical-analysis/index?site=sys_email&order_name=bounceOrder&order=1'.$searchCondition;
                if(isset($formData['order_name']) && $formData['order_name'] == 'bounceOrder' && $formData['order'] == 1) {
                    $orderLink['bounceOrder'] = '/statistical-analysis/index?site=sys_email&order_name=bounceOrder&order=2'.$searchCondition;
                }
                // 拒绝率排序连接
                $orderLink['rejectOrder'] = '/statistical-analysis/index?site=sys_email&order_name=rejectOrder&order=1'.$searchCondition;
                if(isset($formData['order_name']) && $formData['order_name'] == 'rejectOrder' && $formData['order'] == 1) {
                    $orderLink['rejectOrder'] = '/statistical-analysis/index?site=sys_email&order_name=rejectOrder&order=2'.$searchCondition;
                }
                // 投诉率排序连接
                $orderLink['complaintOrder'] = '/statistical-analysis/index?site=sys_email&order_name=complaintOrder&order=1'.$searchCondition;
                if(isset($formData['order_name']) && $formData['order_name'] == 'complaintOrder' && $formData['order'] == 1) {
                    $orderLink['complaintOrder'] = '/statistical-analysis/index?site=sys_email&order_name=complaintOrder&order=2'.$searchCondition;
                }

                // 保存用户结果数据
                $data = $this->getStatisticsData($formData);
                $totalCount = $data['totalCount'];

                // 分页相关
                $pages = new Pagination(['totalCount' =>$totalCount, 'pageSize' => $pageSize, 'defaultPageSize' => $pageSize]);
                $pageCount = $pages->getPageCount(); //页数

                // 渲染模版
                return $this->render('list',[
                    'data' => $data['statistics-data'],
                    'total' => $data['statistics-total'],
                    'site' => $site,
                    'regSource' => $reg_source,
                    'pages' => $pages,
                    'orderLink'  => $orderLink,
                    // 'pageOther' => $pageOther,
                    'formData' => $formData,
                    // 'curPageIds' => $curPageIds,           
                ]);
        }
    }
    
    
    /**
     * 新的邮件转化统计列表
     */
    public function actionNewIndex()
    {
        $data = [];
        $siteInfo = [];
        
        $defaultPageSize = 20;
        $mothod = strtolower(Yii::$app->request->getMethod());
        
        // 查询网站相关的数据
        $formData = Yii::$app->request->$mothod();
        $site = trim($formData['site']);
        
       // 当前用户所有有权限的站点
        $siteInfo_tmp = Yii::$app->authority->getSysEmailAccessSite();
        
        if (isset($siteInfo_tmp) && is_array($siteInfo_tmp)) {
            foreach ($siteInfo_tmp as $siteInfo_tmp_k => $siteInfo_tmp_v) {
                $siteInfo[$siteInfo_tmp_v['site_id']] = $siteInfo_tmp_v['name'];
            }
        }
        
        // 默认的时间数据
        if (!empty($formData['reservation_time'])){
            $reservationTimeArr = explode(' - ',$formData['reservation_time']);
            $formData['startTime'] = trim($reservationTimeArr[0]);
            $formData['endTime'] = trim($reservationTimeArr[1]);
        }
        // 当前时间 
        $curTime = \yii::$app->bjTime->time();
        $timeYearMonthStart = \yii::$app->bjTime->date('Y-m-d',$curTime-30*24*60*60);
        $timeYearMonthCurrent = \yii::$app->bjTime->date('Y-m-d',$curTime);
        $formData['startTime'] = !empty($formData['startTime']) ? $formData['startTime'] : $timeYearMonthStart;
        $formData['endTime'] = !empty($formData['endTime']) ? $formData['endTime'] : $timeYearMonthCurrent;
        
        // 当前用户所有有权限的站点结束
        $formData['siteInfo'] = $siteInfo;
        
        // 当前搜索哪个网站的用户信息
        $formData['site_id'] = isset($formData['site_id']) ? intval($formData['site_id']) : array_keys($siteInfo)[0];
        
        $formData['templateType'][0] = '全部';
        
        $reg_source= ['ALL'=> '全部'];
        if ($formData['site_id']) {
            $flag = Yii::$app->authority->judgeSysEmailHaveSite($formData['site_id']);
            if (!$flag) {
                 $this->sysMsg('警告', '你的账号没有权限!',$params = [ 'type' => 'error', 'links' => [ ['name'=>'返回上一页' , 'url'=>'javascript:history.go(-1)'] ] ]);
            }
            // 邮件类型
            $templateModel = new MailTemplateModel;
            $templateDatas = $templateModel->newGetTemplateType($formData['site_id']);
            
            $templateInfo = [];
            foreach($templateDatas as $templateData) {
                $formData['templateType'][$templateData['id']] = $templateData['name'];
                $templateInfo[$templateData['id']] = ['name' => $templateData['name'], 'uuqid' => $templateData['uuqid']];
            }
            
            $reg_source = TemplateMarket::regSourceList($formData['site_id']);
        }
        
        $formData['template_id'] = isset($formData['template_id']) ? intval($formData['template_id']) : 0;
        $formData['reg_source'] = isset($formData['reg_source']) ? trim($formData['reg_source']) : 'ALL';
        
        // 分页参数相关
        $page = Yii::$app->request->$mothod('page') ? Yii::$app->request->$mothod('page') : 1;
        $pageSize = Yii::$app->request->$mothod('pagesize') ? Yii::$app->request->$mothod('pagesize') : $defaultPageSize;
        $formData['page'] = $page;
        $formData['pageSize'] = $pageSize;
        
        // 导出数据
        if(isset($formData['downLoad']) && $formData['downLoad']) {
                $data = $this->newDownLoadStatisticsData($formData,$templateInfo);
                if($data['code'] != 0) {
                    $this->sysMsg('警告',$data['msg'],$params = [ 'type' => 'error', 'links' => [ ['name'=>'返回上一页' , 'url'=>'javascript:history.go(-1)'] ] ]);
                }
                return;
        } else {
        
        
                $searchCondition = '';
                if($formData['site_id']) {
                    $searchCondition .= '&site_id='.$formData['site_id'];
                }
                if(isset($formData['reservation_time'])) {
                    $searchCondition .= '&reservation_time='. $formData['reservation_time'];
                }
                if(isset($formData['template_id'])) {
                    $searchCondition .= '&template_id='. $formData['template_id'];
                }
                if(isset($formData['reg_source']) && $formData['reg_source'] != 'ALL') {
                    $searchCondition .= '&amp;reg_source='. $formData['reg_source'];
                }
                
                // 送达率排序连接
                $orderLink['sendSuccessOrder'] = '/statistical-analysis/new-index?site=sys_email&order_name=sendSuccessOrder&order=1'.$searchCondition;
                if(isset($formData['order_name']) && $formData['order_name'] == 'sendSuccessOrder' && $formData['order'] == 1) {
                    $orderLink['sendSuccessOrder'] = '/statistical-analysis/new-index?site=sys_email&order_name=sendSuccessOrder&order=2'.$searchCondition;
                }
                // 送达率排序连接
                $orderLink['sendTimeLongOrder'] = '/statistical-analysis/new-index?site=sys_email&order_name=sendTimeLongOrder&order=1'.$searchCondition;
                if(isset($formData['order_name']) && $formData['order_name'] == 'sendTimeLongOrder' && $formData['order'] == 1) {
                    $orderLink['sendTimeLongOrder'] = '/statistical-analysis/new-index?site=sys_email&order_name=sendTimeLongOrder&order=2'.$searchCondition;
                }
                // 打开率排序连接
                $orderLink['openOrder'] = '/statistical-analysis/new-index?site=sys_email&order_name=openOrder&order=1'.$searchCondition;
                if(isset($formData['order_name']) && $formData['order_name'] == 'openOrder' && $formData['order'] == 1) {
                    $orderLink['openOrder'] = '/statistical-analysis/new-index?site=sys_email&order_name=openOrder&order=2'.$searchCondition;
                }
                // 弹回率排序连接
                $orderLink['bounceOrder'] = '/statistical-analysis/new-index?site=sys_email&order_name=bounceOrder&order=1'.$searchCondition;
                if(isset($formData['order_name']) && $formData['order_name'] == 'bounceOrder' && $formData['order'] == 1) {
                    $orderLink['bounceOrder'] = '/statistical-analysis/new-index?site=sys_email&order_name=bounceOrder&order=2'.$searchCondition;
                }
                // 拒绝率排序连接
                $orderLink['rejectOrder'] = '/statistical-analysis/new-index?site=sys_email&order_name=rejectOrder&order=1'.$searchCondition;
                if(isset($formData['order_name']) && $formData['order_name'] == 'rejectOrder' && $formData['order'] == 1) {
                    $orderLink['rejectOrder'] = '/statistical-analysis/new-index?site=sys_email&order_name=rejectOrder&order=2'.$searchCondition;
                }
                // 投诉率排序连接
                $orderLink['complaintOrder'] = '/statistical-analysis/new-index?site=sys_email&order_name=complaintOrder&order=1'.$searchCondition;
                if(isset($formData['order_name']) && $formData['order_name'] == 'complaintOrder' && $formData['order'] == 1) {
                    $orderLink['complaintOrder'] = '/statistical-analysis/new-index?site=sys_email&order_name=complaintOrder&order=2'.$searchCondition;
                }

                // 保存用户结果数据
                $data = $this->newGetStatisticsData($formData);
                $totalCount = $data['totalCount'];

                // 分页相关
                $pages = new Pagination(['totalCount' =>$totalCount, 'pageSize' => $pageSize, 'defaultPageSize' => $pageSize]);
                $pageCount = $pages->getPageCount(); //页数

                // 渲染模版
                return $this->render('new-list',[
                    'data' => $data['statistics-data'],
                    'total' => $data['statistics-total'],
                    'site' => $site,
                    'pages' => $pages,
                    'regSource' => $reg_source,
                    'orderLink'  => $orderLink,
                    'templateInfo'  => $templateInfo,
                    // 'pageOther' => $pageOther,
                    'formData' => $formData,
                    // 'curPageIds' => $curPageIds,           
                ]);
        }
    }
    
    /*
     * 下载统计数据
     * 
     */
    public function newDownLoadStatisticsData($formData, $templateInfo)
    {
        
        $exportMaxDatas = 10000;  // 最大的导出数据量
        $pageSize = 2000; // 每次处理的数据量
        
        $curBjTime = \yii::$app->bjTime->time();
        
        
        $returnData = $templateIds = array();
        $return = ['code' => 0,'msg' => 'success'];
        // 保存where条件
        $where = " 1 = 1 ";
        if ($formData['site_id']) {
            $where .= " and site_id = " .$formData['site_id'];
            
                // 邮箱类型条件
                if ($formData['template_id']) {

                    $where .= " and template_id={$formData['template_id']} ";
                }

                if(isset($formData['reg_source']) && $formData['reg_source'] != 'ALL') {
                    $where .= " and pipeline_code = '".trim($formData['reg_source'])."'";
                }

        
                $where .= " and send_date >= '{$formData['startTime']}' and send_date <= '{$formData['endTime']}'";


                // 查询当前条件下的数据总数
                $getTotalSql = "select count(*) from (select template_id from email_statistics_new where {$where} group by template_id) res";
                $totalCount = Yii::$app->db->createCommand($getTotalSql)->queryScalar();
                if($totalCount >= $exportMaxDatas) {
                    $return['code'] = 2;
                    $return['msg'] = '导出数据文件已超上限（1万）,导出失败！<br/>*可将数据拆小些导出';
                    return $return;
                }
                
                $order = ' order by send_date desc ';
                
                $returnData['statistics-data'] = [];

                // 查询特定条件下每个模板对应的相关统计数据（所有的模板）
                $sql = "select template_id,sum(send_total_nums) send_total_nums,sum(send_success_nums) send_success_nums,sum(total_send_time_long) total_send_time_long,sum(complaint_nums) complaint_nums,sum(bounce_nums) bounce_nums,sum(reject_nums) reject_nums,sum(open_nums) open_nums from email_statistics_new where {$where} group by template_id $order";

                $returnData['statistics-data'] = yii::$app->db->createCommand($sql)->queryAll();
                
                if($returnData['statistics-data']) {
                    $returnData['statistics-data'] = array_chunk($returnData['statistics-data'],$pageSize);

                    $downloadFilename = $formData['siteInfo'][$formData['site_id']].'_MCA_'.$formData['startTime'].'_'.$formData['endTime'].'.csv';

                    // 下载文件输出头
                    header('Content-Description: File Transfer');
                    header('Content-Type: application/vnd.ms-excel');
                    header('Content-Disposition: attachment; filename="'. $downloadFilename .'"');
                    header('Expires: 0');
                    header('Cache-Control: must-revalidate');
                    header('Pragma: public');

                    $fp = fopen('php://output', 'a');//打开output流
                    $title = ['邮件名称','邮件类型','推送用户量','发送成功','送达率','平均发送时长','打开用户','打开率','弹回','弹回率','拒绝','拒绝率','投诉','投诉率'];

                    fputcsv($fp, $title);//将数据名称格式化为CSV格式并写入到output流中
                
                    foreach($returnData['statistics-data'] as $chunkDatas) {

                        foreach ($chunkDatas as $datak =>  $datav) {

                            $exportData = [];
                            $exportData[] = isset($templateInfo[$datav['template_id']]['name']) ? $templateInfo[$datav['template_id']]['name'] : '--';
                            $exportData[] = isset($templateInfo[$datav['template_id']]['uuqid']) ? $templateInfo[$datav['template_id']]['uuqid'] : '--';
                            $exportData[] = $datav['send_total_nums'];
                            $exportData[] = $datav['send_success_nums'];
                            $send_ok_rate = $datav['send_total_nums']>0 ? number_format( $datav['send_success_nums'] / $datav['send_total_nums'] , 4)*100 : 0;
                            $exportData[] = $send_ok_rate.'%';

                            // 发送成功率
                            if($datav['send_total_nums']) {
                                $avg_send_time_long = number_format( $datav['total_send_time_long'] / $datav['send_total_nums'] );
                                if($avg_send_time_long < 60) {
                                    $avgSendLong = $avg_send_time_long."秒";
                                } elseif ($avg_send_time_long >= 60 && $avg_send_time_long < 3600) {
                                    $minites = floor($avg_send_time_long/60);
                                    $seconds = $avg_send_time_long - $minites*60;
                                    $avgSendLong = $minites.'分钟'.$seconds.'秒';
                                }

                            } else {
                                $avgSendLong = '0秒';
                            }
                            $exportData[] = $avgSendLong;
                            $exportData[] = $datav['open_nums'];

                            if($datav['send_success_nums']) {
                                $all_open_rate = number_format( $datav['open_nums']/$datav['send_success_nums'] , 4)*100;
                                $all_complaint_rate = number_format( $datav['complaint_nums']/$datav['send_success_nums'] , 4)*100;
                                $all_bounce_rate = number_format( $datav['bounce_nums']/$datav['send_success_nums'] , 4)*100;
                                $all_reject_rate = number_format( $datav['reject_nums']/$datav['send_success_nums'] , 4)*100;
                            } else {
                                $all_open_rate = 0;
                                $all_complaint_rate = 0;
                                $all_bounce_rate = 0;
                                $all_reject_rate = 0;
                            }
                            $exportData[] = $all_open_rate.'%';
                            $exportData[] = $datav['bounce_nums'];
                            $exportData[] = $all_bounce_rate.'%';
                            $exportData[] = $datav['reject_nums'];
                            $exportData[] = $all_reject_rate.'%';
                            $exportData[] = $datav['complaint_nums'];
                            $exportData[] = $all_complaint_rate.'%';

                            fputcsv($fp, $exportData);//将数据格式化为CSV格式并写入到output流中
                        }

                        //刷新输出缓冲到浏览器
                        ob_flush();
                        flush();//必须同时使用 ob_flush() 和flush() 函数来刷新输出缓冲。
                    }
                    fclose($fp);
                    return $return;
                } else {
                    $return['code'] = 2;
                    $return['msg'] = '筛选数据为0,导出失败！';
                    return $return;
                }
                
        } else {
            $return['code'] = 2;
            $return['msg'] = '导出数据文件参数不存在,导出失败！';
            return $return;
        }
        
    }
    
    /*
     * 下载统计数据
     * 
     */
    public function downLoadStatisticsData($formData)
    {
        
        $exportMaxDatas = 10000;  // 最大的导出数据量
        $pageSize = 2000; // 每次处理的数据量
        
        $curBjTime = \yii::$app->bjTime->time();
        
        
        $returnData = $templateIds = array();
        $return = ['code' => 0,'msg' => 'success'];
        // 保存where条件
        $where = " 1 = 1 ";
        if ($formData['site_id']) {
            $where .= " and site_id = " .$formData['site_id'];
            $templateCon = [];
            // 邮箱类型条件
            if ($formData['template_type_id']) {
                
                $templateCon[] = " ty.id={$formData['template_type_id']} ";
            }
            
            if(isset($formData['reg_source']) && $formData['reg_source'] != 'ALL') {
                $templateCon[] = " te.reg_source = '".trim($formData['reg_source'])."'";
            }
            
            if($templateCon) {
                $templateInfoSql = "select te.id from template_type ty join template te on ty.sys_template_type_uuqid=te.uuqid where ty.site_id = {$formData['site_id']} and ".join(' and ',$templateCon);
                $templateInfo =  yii::$app->db->createCommand($templateInfoSql)->queryAll();
                foreach($templateInfo as $template) {
                    $templateIds[] = $template['id'];
                }
                $where .= !empty($templateIds) ? ' and template_id in ('.join(',',$templateIds).')' : ' and template_id = 0 ';
            }
            
        
                $where .= " and send_date >= '{$formData['startTime']}' and send_date <= '{$formData['endTime']}'";


                // 查询当前条件下的数据总数
                $getTotalSql = "select count(*) from (select template_id from email_statistics where {$where} group by template_id) res";
                $totalCount = Yii::$app->db->createCommand($getTotalSql)->queryScalar();
                if($totalCount >= $exportMaxDatas) {
                    $return['code'] = 2;
                    $return['msg'] = '导出数据文件已超上限（1万）,导出失败！<br/>*可将数据拆小些导出';
                    return $return;
                }
                
                $order = ' order by send_date desc ';
                
                $returnData['statistics-data'] = [];

                // 查询特定条件下每个模板对应的相关统计数据（所有的模板）
                $sql = "select template_id,sum(send_total_nums) send_total_nums,sum(send_success_nums) send_success_nums,sum(total_send_time_long) total_send_time_long,sum(complaint_nums) complaint_nums,sum(bounce_nums) bounce_nums,sum(reject_nums) reject_nums,sum(open_nums) open_nums from email_statistics where {$where} group by template_id $order";

                $returnData['statistics-data'] = yii::$app->db->createCommand($sql)->queryAll();
                $templateIdArr = $templateInfoArr = [];
                foreach($returnData['statistics-data'] as $datak => $datav) {
                    $templateIdArr[] = $datav['template_id'];
                }


                if($templateIdArr) {
                // 获取模板信息
                    $getTemplates = "select id,note,uuqid from template where id in(".join(',',$templateIdArr).")";
                    $templates = yii::$app->db->createCommand($getTemplates)->queryAll();
                    foreach($templates as $template) {
                        $templateInfoArr[$template['id']] = ['note' => $template['note'], 'uuqid' => $template['uuqid']];
                    }
                }
                if($returnData['statistics-data']) {
                    $returnData['statistics-data'] = array_chunk($returnData['statistics-data'],$pageSize);

                    $downloadFilename = $formData['siteInfo'][$formData['site_id']].'_MCA_'.$formData['startTime'].'_'.$formData['endTime'].'.csv';

                    // 下载文件输出头
                    header('Content-Description: File Transfer');
                    header('Content-Type: application/vnd.ms-excel');
                    header('Content-Disposition: attachment; filename="'. $downloadFilename .'"');
                    header('Expires: 0');
                    header('Cache-Control: must-revalidate');
                    header('Pragma: public');

                    $fp = fopen('php://output', 'a');//打开output流
                    $title = ['邮件名称','邮件类型','推送用户量','发送成功','送达率','平均发送时长','打开用户','打开率','弹回','弹回率','拒绝','拒绝率','投诉','投诉率'];

                    fputcsv($fp, $title);//将数据名称格式化为CSV格式并写入到output流中
                
                    foreach($returnData['statistics-data'] as $chunkDatas) {

                        foreach ($chunkDatas as $datak =>  $datav) {

                            $exportData = [];
                            $exportData[] = isset($templateInfoArr[$datav['template_id']]['uuqid']) ? $templateInfoArr[$datav['template_id']]['uuqid'] : '--';
                            $exportData[] = isset($templateInfoArr[$datav['template_id']]['note']) ? $templateInfoArr[$datav['template_id']]['note'] : '--';
                            $exportData[] = $datav['send_total_nums'];
                            $exportData[] = $datav['send_success_nums'];
                            $send_ok_rate = $datav['send_total_nums']>0 ? number_format( $datav['send_success_nums'] / $datav['send_total_nums'] , 4)*100 : 0;
                            $exportData[] = $send_ok_rate.'%';

                            // 发送成功率
                            if($datav['send_total_nums']) {
                                $avg_send_time_long = number_format( $datav['total_send_time_long'] / $datav['send_total_nums'] );
                                if($avg_send_time_long < 60) {
                                    $avgSendLong = $avg_send_time_long."秒";
                                } elseif ($avg_send_time_long >= 60 && $avg_send_time_long < 3600) {
                                    $minites = floor($avg_send_time_long/60);
                                    $seconds = $avg_send_time_long - $minites*60;
                                    $avgSendLong = $minites.'分钟'.$seconds.'秒';
                                }

                            } else {
                                $avgSendLong = '0秒';
                            }
                            $exportData[] = $avgSendLong;
                            $exportData[] = $datav['open_nums'];

                            if($datav['send_success_nums']) {
                                $all_open_rate = number_format( $datav['open_nums']/$datav['send_success_nums'] , 4)*100;
                                $all_complaint_rate = number_format( $datav['complaint_nums']/$datav['send_success_nums'] , 4)*100;
                                $all_bounce_rate = number_format( $datav['bounce_nums']/$datav['send_success_nums'] , 4)*100;
                                $all_reject_rate = number_format( $datav['reject_nums']/$datav['send_success_nums'] , 4)*100;
                            } else {
                                $all_open_rate = 0;
                                $all_complaint_rate = 0;
                                $all_bounce_rate = 0;
                                $all_reject_rate = 0;
                            }
                            $exportData[] = $all_open_rate.'%';
                            $exportData[] = $datav['bounce_nums'];
                            $exportData[] = $all_bounce_rate.'%';
                            $exportData[] = $datav['reject_nums'];
                            $exportData[] = $all_reject_rate.'%';
                            $exportData[] = $datav['complaint_nums'];
                            $exportData[] = $all_complaint_rate.'%';

                            fputcsv($fp, $exportData);//将数据格式化为CSV格式并写入到output流中
                        }

                        //刷新输出缓冲到浏览器
                        ob_flush();
                        flush();//必须同时使用 ob_flush() 和flush() 函数来刷新输出缓冲。
                    }
                    fclose($fp);
                    return $return;
                } else {
                    $return['code'] = 2;
                    $return['msg'] = '筛选数据为0,导出失败！';
                    return $return;
                }
                
        } else {
            $return['code'] = 2;
            $return['msg'] = '导出数据文件参数不存在,导出失败！';
            return $return;
        }
        
    }
    
    /*
     * 获取统计数据
     * 
     */
    public function getStatisticsData($formData)
    {
        $curBjTime = \yii::$app->bjTime->time();
        
        
        $returnData = $templateIds = array();
        
        // 保存where条件
        $where = " 1 = 1 ";
        if ($formData['site_id']) {
            $where .= " and site_id = " .$formData['site_id'];
            $templateCon = [];
            // 邮箱类型条件
            if ($formData['template_type_id']) {
                
                $templateCon[] = " ty.id={$formData['template_type_id']} ";
            }
            
            if(isset($formData['reg_source']) && $formData['reg_source'] != 'ALL') {
                $templateCon[] = " te.reg_source = '".trim($formData['reg_source'])."'";
            }
            
            if($templateCon) {
                $templateInfoSql = "select te.id from template_type ty join template te on ty.sys_template_type_uuqid=te.uuqid where ty.site_id = {$formData['site_id']} and ".join(' and ',$templateCon);
                $templateInfo =  yii::$app->db->createCommand($templateInfoSql)->queryAll();
                foreach($templateInfo as $template) {
                    $templateIds[] = $template['id'];
                }
                $where .= !empty($templateIds) ? ' and template_id in ('.join(',',$templateIds).')' : ' and template_id = 0 ';
            }
            
        }
        
        
        
        $where .= " and send_date >= '{$formData['startTime']}' and send_date <= '{$formData['endTime']}'";
       
        
        // 查询当前条件下的数据总数
        $getTotalSql = "select count(*) from (select template_id from email_statistics where {$where} group by template_id) res";
        $totalCount = Yii::$app->db->createCommand($getTotalSql)->queryScalar();
        $returnData['totalCount'] = $totalCount;
        $returnData['statistics-data'] = $returnData['statistics-total'] = [];
        
        $order = ' order by send_date desc ';
        if(isset($formData['order_name']) && $formData['order_name'] == 'sendSuccessOrder') {
            if($formData['order'] == 1) {
                $order = ' order by send_success_rate desc ';
            } else if($formData['order'] == 2) {
                $order = ' order by send_success_rate ';
            }
        }
        if(isset($formData['order_name']) && $formData['order_name'] == 'sendTimeLongOrder') {
            if($formData['order'] == 1) {
                $order = ' order by avg_send_time_long desc ';
            } else if($formData['order'] == 2) {
                $order = ' order by avg_send_time_long ';
            }
        }
        if(isset($formData['order_name']) && $formData['order_name'] == 'openOrder') {
            if($formData['order'] == 1) {
                $order = ' order by open_rate desc ';
            } else if($formData['order'] == 2) {
                $order = ' order by open_rate ';
            }
        }
        if(isset($formData['order_name']) && $formData['order_name'] == 'bounceOrder') {
            if($formData['order'] == 1) {
                $order = ' order by bounce_rate desc ';
            } else if($formData['order'] == 2) {
                $order = ' order by bounce_rate ';
            }
        }
        if(isset($formData['order_name']) && $formData['order_name'] == 'rejectOrder') {
            if($formData['order'] == 1) {
                $order = ' order by reject_rate desc ';
            } else if($formData['order'] == 2) {
                $order = ' order by reject_rate ';
            }
        }
        if(isset($formData['order_name']) && $formData['order_name'] == 'complaintOrder') {
            if($formData['order'] == 1) {
                $order = ' order by complaint_rate desc ';
            } else if($formData['order'] == 2) {
                $order = ' order by complaint_rate ';
            }
        }
        
        
        // 分页数
        $pageNum =  ceil($totalCount / $formData['pageSize']);
        $startPage = intval($formData['page'])-1;
        if ($startPage >= $pageNum - 1) {
            $startPage = $pageNum - 1;
        }
        $startPage = $startPage>=0 ? $startPage : 0;

        $offset = $startPage * $formData['pageSize'];
        
        // 查询特定条件下每个模板对应的相关统计数据（所有的模板）
        $sql = "select template_id,sum(send_total_nums) send_total_nums,sum(send_success_nums) send_success_nums,sum(total_send_time_long) total_send_time_long,sum(complaint_nums) complaint_nums,sum(bounce_nums) bounce_nums,sum(reject_nums) reject_nums,sum(open_nums) open_nums,sum(send_success_nums)/sum(send_total_nums) send_success_rate,sum(total_send_time_long)/sum(send_total_nums) avg_send_time_long,sum(complaint_nums)/sum(send_success_nums) complaint_rate,sum(bounce_nums)/sum(send_success_nums) bounce_rate,sum(reject_nums)/sum(send_success_nums) reject_rate,sum(open_nums)/sum(send_success_nums) open_rate from email_statistics where {$where} group by template_id $order LIMIT {$offset},{$formData['pageSize']}";
        
        $returnData['statistics-data'] = yii::$app->db->createCommand($sql)->queryAll();
        $templateIdArr = $templateInfoArr = [];
        foreach($returnData['statistics-data'] as $datak => $datav) {
            $templateIdArr[] = $datav['template_id'];
        }
        
        // 获取当前条件下总的数据量
        $getTotal = "select sum(send_total_nums) send_total_nums,sum(send_success_nums) send_success_nums,sum(total_send_time_long) total_send_time_long,sum(complaint_nums) complaint_nums,sum(bounce_nums) bounce_nums,sum(reject_nums) reject_nums,sum(open_nums) open_nums from email_statistics where {$where}";
        $returnData['statistics-total'] = yii::$app->db->createCommand($getTotal)->queryOne();
        
        if($returnData['statistics-total']['send_total_nums']) {
            $returnData['statistics-total']['send_ok_rate'] = number_format( $returnData['statistics-total']['send_success_nums'] / $returnData['statistics-total']['send_total_nums'] , 4)*100;
            $avg_send_time_long = number_format( $returnData['statistics-total']['total_send_time_long'] / $returnData['statistics-total']['send_total_nums'], 0, '', '');
            if($avg_send_time_long < 60) {
                $returnData['statistics-total']['avg_send_time_long'] = $avg_send_time_long."秒";
            } elseif ($avg_send_time_long >= 60 && $avg_send_time_long < 3600) {
                $minites = floor($avg_send_time_long/60);
                $seconds = $avg_send_time_long - $minites*60;
                $returnData['statistics-total']['avg_send_time_long'] = $minites.'分钟'.$seconds.'秒';
            }
            
        } else {
            $returnData['statistics-total']['send_ok_rate'] = 0;
            $returnData['statistics-total']['avg_send_time_long'] = '0秒';
        }
        
        if($returnData['statistics-total']['send_success_nums']) {
            $returnData['statistics-total']['complaint_rate'] = number_format( $returnData['statistics-total']['complaint_nums'] / $returnData['statistics-total']['send_success_nums'] , 4)*100;
            $returnData['statistics-total']['bounce_rate'] = number_format( $returnData['statistics-total']['bounce_nums'] / $returnData['statistics-total']['send_success_nums'] , 4)*100;
            $returnData['statistics-total']['reject_rate'] = number_format( $returnData['statistics-total']['reject_nums'] / $returnData['statistics-total']['send_success_nums'] , 4)*100;
            $returnData['statistics-total']['open_rate'] = number_format( $returnData['statistics-total']['open_nums'] / $returnData['statistics-total']['send_success_nums'] , 4)*100;
        } else {
            $returnData['statistics-total']['complaint_rate'] = 0;
            $returnData['statistics-total']['bounce_rate'] = 0;
            $returnData['statistics-total']['reject_rate'] = 0;
            $returnData['statistics-total']['open_rate'] = 0;
        }
        $returnData['statistics-total']['stat']['open'] = '';
        $emailMarket = new EmailMarket();
        
        $totalOpenTerminalData = $totalOpenEmailTypeData = [];
        if($templateIdArr) {
        // 获取模板信息
            $getTemplates = "select id,note,uuqid from template where id in(".join(',',$templateIdArr).")";
            $templates = yii::$app->db->createCommand($getTemplates)->queryAll();
            foreach($templates as $template) {
                $templateInfoArr[$template['id']] = ['note' => $template['note'], 'uuqid' => $template['uuqid']];
            }
            //获取各个模板的打开设备总数
            $openCountData = yii::$app->db->createCommand('select template_id,open_terminal,sum(open_count) as open_count from email_open_statistics where '.$where.' group by template_id,open_terminal')->queryAll();
            foreach ($openCountData as $openCountDatum) {
                $openData[$openCountDatum['template_id']][$openCountDatum['open_terminal']] = $openCountDatum['open_count'];
                if(isset($totalOpenTerminalData[$openCountDatum['open_terminal']])) {
                    $totalOpenTerminalData[$openCountDatum['open_terminal']] += $openCountDatum['open_count'];
                } else {
                    $totalOpenTerminalData[$openCountDatum['open_terminal']] = $openCountDatum['open_count'];
                }
                
            }
            //获取各个模板的邮箱类型
            $openCountDataByEmailTypeTmp = yii::$app->db->createCommand('select template_id,email_type,sum(open_nums) as open_count from email_statistics where '.$where.' group by template_id,email_type')->queryAll();
            foreach ($openCountDataByEmailTypeTmp as $openCountDatum) {
                $openCountDataByEmailType[$openCountDatum['template_id']][$openCountDatum['email_type']] = $openCountDatum['open_count'];
                if(isset($totalOpenEmailTypeData[$openCountDatum['email_type']])) {
                    $totalOpenEmailTypeData[$openCountDatum['email_type']] += $openCountDatum['open_count'];
                } else {
                    $totalOpenEmailTypeData[$openCountDatum['email_type']] = $openCountDatum['open_count'];
                }
            }
            $returnData['statistics-total']['stat']['open_terminal'] = $returnData['statistics-total']['stat']['email_type'] = '';
            if($totalOpenTerminalData){
                foreach ($totalOpenTerminalData as $openTerminal => $openTerminalOpenCount) {
                    $openTerminal = $emailMarket->getOpenTerminalName($openTerminal);
                    $openTerminalNumsPercent = $returnData['statistics-total']['open_nums'] > 0 ? number_format($openTerminalOpenCount / $returnData['statistics-total']['open_nums'],4)*100 : 0;
                    $returnData['statistics-total']['stat']['open_terminal'] .= '<tr>
                                                                                                <td class = "firsttd">'.$openTerminal.'</td>
                                                                                                <td class = "secondtd">'.$openTerminalNumsPercent.'%</td>
                                                                                                <td class = "thirdtd">('.$openTerminalOpenCount.')</td>
                                                                                            </tr>';
                }
            }
            if($totalOpenEmailTypeData){
                foreach ($totalOpenEmailTypeData as $openEmailType => $openEmailTypeCount) {
                    $emailTypeName =  $emailMarket->getEmailTypeName($openEmailType);
                    $openEmailTypeNumsPercent = $returnData['statistics-total']['open_nums'] > 0 ? number_format($openEmailTypeCount / $returnData['statistics-total']['open_nums'],4)*100 : 0;
                    $returnData['statistics-total']['stat']['email_type'] .= '<tr>
                                                                                                <td class = "firsttd">'.$emailTypeName.'</td>
                                                                                                <td class = "secondtd">'.$openEmailTypeNumsPercent.'%</td>
                                                                                                <td class = "thirdtd">('.$openEmailTypeCount.')</td>
                                                                                            </tr>';
                }
            }
            $returnData['statistics-total']['stat']['open'] = '<table class="viewstyle"><tr><td colspan="3">邮件类型</td></tr>'.$returnData['statistics-total']['stat']['email_type']. '<tr><td colspan="3">打开设备</td></tr>'.$returnData['statistics-total']['stat']['open_terminal'].'</table>';
            
            
        }
        

        foreach($returnData['statistics-data'] as $datak => $datav) {
            // 发送成功率
            if($datav['send_total_nums']) {
                $returnData['statistics-data'][$datak]['send_ok_rate'] = number_format( $datav['send_success_nums'] / $datav['send_total_nums'] , 4)*100;
                $avg_send_time_long = number_format( $datav['total_send_time_long'] / $datav['send_total_nums'] );
                if($avg_send_time_long < 60) {
                    $returnData['statistics-data'][$datak]['avg_send_time_long'] = $avg_send_time_long."秒";
                } elseif ($avg_send_time_long >= 60 && $avg_send_time_long < 3600) {
                    $minites = floor($avg_send_time_long/60);
                    $seconds = $avg_send_time_long - $minites*60;
                    $returnData['statistics-data'][$datak]['avg_send_time_long'] = $minites.'分钟'.$seconds.'秒';
                }
                
            } else {
                $returnData['statistics-data'][$datak]['send_ok_rate'] = 0;
                $returnData['statistics-data'][$datak]['avg_send_time_long'] = '0秒';
            }
            $returnData['statistics-data'][$datak]['note'] = isset($templateInfoArr[$datav['template_id']]['note']) ? $templateInfoArr[$datav['template_id']]['note'] : '--';
            $returnData['statistics-data'][$datak]['uuqid'] = isset($templateInfoArr[$datav['template_id']]['uuqid']) ? $templateInfoArr[$datav['template_id']]['uuqid'] : '--';
            


            $returnData['statistics-data'][$datak]['stat']['open_terminal'] = '';
            $returnData['statistics-data'][$datak]['stat']['email_type'] = '';
            if(isset($openData[$datav['template_id']])){
                foreach ($openData[$datav['template_id']] as $openTerminal => $openTerminalOpenCount) {
                    $openTerminal = $emailMarket->getOpenTerminalName($openTerminal);
                    $openTerminalNumsPercent = $datav['open_nums'] > 0 ? number_format($openTerminalOpenCount / $datav['open_nums'],4)*100 : 0;
                    $returnData['statistics-data'][$datak]['stat']['open_terminal'] .= '<tr>
                                                                                                <td class = "firsttd">'.$openTerminal.'</td>
                                                                                                <td class = "secondtd">'.$openTerminalNumsPercent.'%</td>
                                                                                                <td class = "thirdtd">('.$openTerminalOpenCount.')</td>
                                                                                            </tr>';
                }
            }
            if(isset($openCountDataByEmailType[$datav['template_id']])){
                foreach ($openCountDataByEmailType[$datav['template_id']] as $openEmailType => $openEmailTypeCount) {
                    $emailTypeName =  $emailMarket->getEmailTypeName($openEmailType);
                    $openEmailTypeNumsPercent = $datav['open_nums'] > 0 ? number_format($openEmailTypeCount / $datav['open_nums'],4)*100 : 0;
                    $returnData['statistics-data'][$datak]['stat']['email_type'] .= '<tr>
                                                                                                <td class = "firsttd">'.$emailTypeName.'</td>
                                                                                                <td class = "secondtd">'.$openEmailTypeNumsPercent.'%</td>
                                                                                                <td class = "thirdtd">('.$openEmailTypeCount.')</td>
                                                                                            </tr>';
                }
            }
            $returnData['statistics-data'][$datak]['stat']['open'] = '<table class="viewstyle"><tr><td colspan="3">邮件类型</td></tr>'.$returnData['statistics-data'][$datak]['stat']['email_type']. '<tr><td colspan="3">打开设备</td></tr>'.$returnData['statistics-data'][$datak]['stat']['open_terminal'].'</table>';
            if($datav['send_success_nums']) {
                //打开率
                $returnData['statistics-data'][$datak]['all_open_rate'] = number_format( $datav['open_nums']/$datav['send_success_nums'] , 4)*100;
                $returnData['statistics-data'][$datak]['all_complaint_rate'] = number_format( $datav['complaint_nums']/$datav['send_success_nums'] , 4)*100;
                $returnData['statistics-data'][$datak]['all_bounce_rate'] = number_format( $datav['bounce_nums']/$datav['send_success_nums'] , 4)*100;
                $returnData['statistics-data'][$datak]['all_reject_rate'] = number_format( $datav['reject_nums']/$datav['send_success_nums'] , 4)*100;
            } else {
                $returnData['statistics-data'][$datak]['all_open_rate'] = 0;
                $returnData['statistics-data'][$datak]['all_complaint_rate'] = 0;
                $returnData['statistics-data'][$datak]['all_bounce_rate'] = 0;
                $returnData['statistics-data'][$datak]['all_reject_rate'] = 0;
            }
            
        }
        
        return $returnData;
    }
    
    /*
     * 获取统计数据
     * 
     */
    public function newGetStatisticsData($formData)
    {
        $curBjTime = \yii::$app->bjTime->time();
        
        
        $returnData = $templateIds = array();
        
        // 保存where条件
        $where = " 1 = 1 ";
        if ($formData['site_id']) {
            $where .= " and site_id = " .$formData['site_id'];
            // 邮箱类型条件
            if ($formData['template_id']) {
                
                $where .= " and template_id={$formData['template_id']} ";
            }
            if(isset($formData['reg_source']) && $formData['reg_source'] != 'ALL') {
                $where .= " and pipeline_code = '".trim($formData['reg_source'])."'";
            }
        }
        
        
        $where .= " and send_date >= '{$formData['startTime']}' and send_date <= '{$formData['endTime']}'";
       
        
        // 查询当前条件下的数据总数
        $getTotalSql = "select count(*) from (select template_id from email_statistics_new where {$where} group by template_id) res";
        $totalCount = Yii::$app->db->createCommand($getTotalSql)->queryScalar();
        $returnData['totalCount'] = $totalCount;
        $returnData['statistics-data'] = $returnData['statistics-total'] = [];
        
        $order = ' order by send_date desc ';
        if(isset($formData['order_name']) && $formData['order_name'] == 'sendSuccessOrder') {
            if($formData['order'] == 1) {
                $order = ' order by send_success_rate desc ';
            } else if($formData['order'] == 2) {
                $order = ' order by send_success_rate ';
            }
        }
        if(isset($formData['order_name']) && $formData['order_name'] == 'sendTimeLongOrder') {
            if($formData['order'] == 1) {
                $order = ' order by avg_send_time_long desc ';
            } else if($formData['order'] == 2) {
                $order = ' order by avg_send_time_long ';
            }
        }
        if(isset($formData['order_name']) && $formData['order_name'] == 'openOrder') {
            if($formData['order'] == 1) {
                $order = ' order by open_rate desc ';
            } else if($formData['order'] == 2) {
                $order = ' order by open_rate ';
            }
        }
        if(isset($formData['order_name']) && $formData['order_name'] == 'bounceOrder') {
            if($formData['order'] == 1) {
                $order = ' order by bounce_rate desc ';
            } else if($formData['order'] == 2) {
                $order = ' order by bounce_rate ';
            }
        }
        if(isset($formData['order_name']) && $formData['order_name'] == 'rejectOrder') {
            if($formData['order'] == 1) {
                $order = ' order by reject_rate desc ';
            } else if($formData['order'] == 2) {
                $order = ' order by reject_rate ';
            }
        }
        if(isset($formData['order_name']) && $formData['order_name'] == 'complaintOrder') {
            if($formData['order'] == 1) {
                $order = ' order by complaint_rate desc ';
            } else if($formData['order'] == 2) {
                $order = ' order by complaint_rate ';
            }
        }
        
        
        // 分页数
        $pageNum =  ceil($totalCount / $formData['pageSize']);
        $startPage = intval($formData['page'])-1;
        if ($startPage >= $pageNum - 1) {
            $startPage = $pageNum - 1;
        }
        $startPage = $startPage>=0 ? $startPage : 0;

        $offset = $startPage * $formData['pageSize'];
        
        // 查询特定条件下每个模板对应的相关统计数据（所有的模板）
        $sql = "select template_id,sum(send_total_nums) send_total_nums,sum(send_success_nums) send_success_nums,sum(total_send_time_long) total_send_time_long,sum(complaint_nums) complaint_nums,sum(bounce_nums) bounce_nums,sum(reject_nums) reject_nums,sum(open_nums) open_nums,sum(send_success_nums)/sum(send_total_nums) send_success_rate,sum(total_send_time_long)/sum(send_total_nums) avg_send_time_long,sum(complaint_nums)/sum(send_success_nums) complaint_rate,sum(bounce_nums)/sum(send_success_nums) bounce_rate,sum(reject_nums)/sum(send_success_nums) reject_rate,sum(open_nums)/sum(send_success_nums) open_rate from email_statistics_new where {$where} group by template_id $order LIMIT {$offset},{$formData['pageSize']}";
        
        $returnData['statistics-data'] = yii::$app->db->createCommand($sql)->queryAll();
        $templateIdArr = $templateInfoArr = [];
        foreach($returnData['statistics-data'] as $datak => $datav) {
            $templateIdArr[] = $datav['template_id'];
        }
        
        // 获取当前条件下总的数据量
        $getTotal = "select sum(send_total_nums) send_total_nums,sum(send_success_nums) send_success_nums,sum(total_send_time_long) total_send_time_long,sum(complaint_nums) complaint_nums,sum(bounce_nums) bounce_nums,sum(reject_nums) reject_nums,sum(open_nums) open_nums from email_statistics_new where {$where}";
        $returnData['statistics-total'] = yii::$app->db->createCommand($getTotal)->queryOne();
        
        if($returnData['statistics-total']['send_total_nums']) {
            $returnData['statistics-total']['send_ok_rate'] = number_format( $returnData['statistics-total']['send_success_nums'] / $returnData['statistics-total']['send_total_nums'] , 4)*100;
            $avg_send_time_long = number_format( $returnData['statistics-total']['total_send_time_long'] / $returnData['statistics-total']['send_total_nums'], 0, '', '');
            if($avg_send_time_long < 60) {
                $returnData['statistics-total']['avg_send_time_long'] = $avg_send_time_long."秒";
            } elseif ($avg_send_time_long >= 60 && $avg_send_time_long < 3600) {
                $minites = floor($avg_send_time_long/60);
                $seconds = $avg_send_time_long - $minites*60;
                $returnData['statistics-total']['avg_send_time_long'] = $minites.'分钟'.$seconds.'秒';
            }
            
        } else {
            $returnData['statistics-total']['send_ok_rate'] = 0;
            $returnData['statistics-total']['avg_send_time_long'] = '0秒';
        }
        
        if($returnData['statistics-total']['send_success_nums']) {
            $returnData['statistics-total']['complaint_rate'] = number_format( $returnData['statistics-total']['complaint_nums'] / $returnData['statistics-total']['send_success_nums'] , 4)*100;
            $returnData['statistics-total']['bounce_rate'] = number_format( $returnData['statistics-total']['bounce_nums'] / $returnData['statistics-total']['send_success_nums'] , 4)*100;
            $returnData['statistics-total']['reject_rate'] = number_format( $returnData['statistics-total']['reject_nums'] / $returnData['statistics-total']['send_success_nums'] , 4)*100;
            $returnData['statistics-total']['open_rate'] = number_format( $returnData['statistics-total']['open_nums'] / $returnData['statistics-total']['send_success_nums'] , 4)*100;
        } else {
            $returnData['statistics-total']['complaint_rate'] = 0;
            $returnData['statistics-total']['bounce_rate'] = 0;
            $returnData['statistics-total']['reject_rate'] = 0;
            $returnData['statistics-total']['open_rate'] = 0;
        }
        $returnData['statistics-total']['stat']['open'] = '';
        $emailMarket = new EmailMarket();
        
        $totalOpenTerminalData = $totalOpenEmailTypeData = [];
        if($templateIdArr) {
        
            //获取各个模板的打开设备总数
            $openCountData = yii::$app->db->createCommand('select template_id,open_terminal,sum(open_count) as open_count from email_open_statistics_new where '.$where.' group by template_id,open_terminal')->queryAll();
            foreach ($openCountData as $openCountDatum) {
                $openData[$openCountDatum['template_id']][$openCountDatum['open_terminal']] = $openCountDatum['open_count'];
                if(isset($totalOpenTerminalData[$openCountDatum['open_terminal']])) {
                    $totalOpenTerminalData[$openCountDatum['open_terminal']] += $openCountDatum['open_count'];
                } else {
                    $totalOpenTerminalData[$openCountDatum['open_terminal']] = $openCountDatum['open_count'];
                }
                
            }
            //获取各个模板的邮箱类型
            $openCountDataByEmailTypeTmp = yii::$app->db->createCommand('select template_id,email_type,sum(open_nums) as open_count from email_statistics_new where '.$where.' group by template_id,email_type')->queryAll();
            foreach ($openCountDataByEmailTypeTmp as $openCountDatum) {
                $openCountDataByEmailType[$openCountDatum['template_id']][$openCountDatum['email_type']] = $openCountDatum['open_count'];
                if(isset($totalOpenEmailTypeData[$openCountDatum['email_type']])) {
                    $totalOpenEmailTypeData[$openCountDatum['email_type']] += $openCountDatum['open_count'];
                } else {
                    $totalOpenEmailTypeData[$openCountDatum['email_type']] = $openCountDatum['open_count'];
                }
            }
            $returnData['statistics-total']['stat']['open_terminal'] = $returnData['statistics-total']['stat']['email_type'] = '';
            if($totalOpenTerminalData){
                foreach ($totalOpenTerminalData as $openTerminal => $openTerminalOpenCount) {
                    $openTerminal = $emailMarket->getOpenTerminalName($openTerminal);
                    $openTerminalNumsPercent = $returnData['statistics-total']['open_nums'] > 0 ? number_format($openTerminalOpenCount / $returnData['statistics-total']['open_nums'],4)*100 : 0;
                    $returnData['statistics-total']['stat']['open_terminal'] .= '<tr>
                                                                                                <td class = "firsttd">'.$openTerminal.'</td>
                                                                                                <td class = "secondtd">'.$openTerminalNumsPercent.'%</td>
                                                                                                <td class = "thirdtd">('.$openTerminalOpenCount.')</td>
                                                                                            </tr>';
                }
            }
            if($totalOpenEmailTypeData){
                foreach ($totalOpenEmailTypeData as $openEmailType => $openEmailTypeCount) {
                    $emailTypeName =  $emailMarket->getEmailTypeName($openEmailType);
                    $openEmailTypeNumsPercent = $returnData['statistics-total']['open_nums'] > 0 ? number_format($openEmailTypeCount / $returnData['statistics-total']['open_nums'],4)*100 : 0;
                    $returnData['statistics-total']['stat']['email_type'] .= '<tr>
                                                                                                <td class = "firsttd">'.$emailTypeName.'</td>
                                                                                                <td class = "secondtd">'.$openEmailTypeNumsPercent.'%</td>
                                                                                                <td class = "thirdtd">('.$openEmailTypeCount.')</td>
                                                                                            </tr>';
                }
            }
            $returnData['statistics-total']['stat']['open'] = '<table class="viewstyle"><tr><td colspan="3">邮件类型</td></tr>'.$returnData['statistics-total']['stat']['email_type']. '<tr><td colspan="3">打开设备</td></tr>'.$returnData['statistics-total']['stat']['open_terminal'].'</table>';
            
            
        }
        

        foreach($returnData['statistics-data'] as $datak => $datav) {
            // 发送成功率
            if($datav['send_total_nums']) {
                $returnData['statistics-data'][$datak]['send_ok_rate'] = number_format( $datav['send_success_nums'] / $datav['send_total_nums'] , 4)*100;
                $avg_send_time_long = number_format( $datav['total_send_time_long'] / $datav['send_total_nums'] );
                if($avg_send_time_long < 60) {
                    $returnData['statistics-data'][$datak]['avg_send_time_long'] = $avg_send_time_long."秒";
                } elseif ($avg_send_time_long >= 60 && $avg_send_time_long < 3600) {
                    $minites = floor($avg_send_time_long/60);
                    $seconds = $avg_send_time_long - $minites*60;
                    $returnData['statistics-data'][$datak]['avg_send_time_long'] = $minites.'分钟'.$seconds.'秒';
                }
                
            } else {
                $returnData['statistics-data'][$datak]['send_ok_rate'] = 0;
                $returnData['statistics-data'][$datak]['avg_send_time_long'] = '0秒';
            }
            


            $returnData['statistics-data'][$datak]['stat']['open_terminal'] = '';
            $returnData['statistics-data'][$datak]['stat']['email_type'] = '';
            if(isset($openData[$datav['template_id']])){
                foreach ($openData[$datav['template_id']] as $openTerminal => $openTerminalOpenCount) {
                    $openTerminal = $emailMarket->getOpenTerminalName($openTerminal);
                    $openTerminalNumsPercent = $datav['open_nums'] > 0 ? number_format($openTerminalOpenCount / $datav['open_nums'],4)*100 : 0;
                    $returnData['statistics-data'][$datak]['stat']['open_terminal'] .= '<tr>
                                                                                                <td class = "firsttd">'.$openTerminal.'</td>
                                                                                                <td class = "secondtd">'.$openTerminalNumsPercent.'%</td>
                                                                                                <td class = "thirdtd">('.$openTerminalOpenCount.')</td>
                                                                                            </tr>';
                }
            }
            if(isset($openCountDataByEmailType[$datav['template_id']])){
                foreach ($openCountDataByEmailType[$datav['template_id']] as $openEmailType => $openEmailTypeCount) {
                    $emailTypeName =  $emailMarket->getEmailTypeName($openEmailType);
                    $openEmailTypeNumsPercent = $datav['open_nums'] > 0 ? number_format($openEmailTypeCount / $datav['open_nums'],4)*100 : 0;
                    $returnData['statistics-data'][$datak]['stat']['email_type'] .= '<tr>
                                                                                                <td class = "firsttd">'.$emailTypeName.'</td>
                                                                                                <td class = "secondtd">'.$openEmailTypeNumsPercent.'%</td>
                                                                                                <td class = "thirdtd">('.$openEmailTypeCount.')</td>
                                                                                            </tr>';
                }
            }
            $returnData['statistics-data'][$datak]['stat']['open'] = '<table class="viewstyle"><tr><td colspan="3">邮件类型</td></tr>'.$returnData['statistics-data'][$datak]['stat']['email_type']. '<tr><td colspan="3">打开设备</td></tr>'.$returnData['statistics-data'][$datak]['stat']['open_terminal'].'</table>';
            if($datav['send_success_nums']) {
                //打开率
                $returnData['statistics-data'][$datak]['all_open_rate'] = number_format( $datav['open_nums']/$datav['send_success_nums'] , 4)*100;
                $returnData['statistics-data'][$datak]['all_complaint_rate'] = number_format( $datav['complaint_nums']/$datav['send_success_nums'] , 4)*100;
                $returnData['statistics-data'][$datak]['all_bounce_rate'] = number_format( $datav['bounce_nums']/$datav['send_success_nums'] , 4)*100;
                $returnData['statistics-data'][$datak]['all_reject_rate'] = number_format( $datav['reject_nums']/$datav['send_success_nums'] , 4)*100;
            } else {
                $returnData['statistics-data'][$datak]['all_open_rate'] = 0;
                $returnData['statistics-data'][$datak]['all_complaint_rate'] = 0;
                $returnData['statistics-data'][$datak]['all_bounce_rate'] = 0;
                $returnData['statistics-data'][$datak]['all_reject_rate'] = 0;
            }
            
        }
        
        return $returnData;
    }
    
    
    /*
     * 异步获取网站对应的所有邮件类型
     */
    public function actionGetTemplateTypeBySiteid()
    {
        $site_id = intval(Yii::$app->request->get('site_id'));
        $reg_source = trim(Yii::$app->request->get('reg_source'));
        $return[0] = '全部'; 
        if($site_id) {
            $templateModel = new MailTemplateModel;
            $templateTypes = $templateModel->getTemplateType($site_id, $reg_source);
            foreach($templateTypes as $templateType) {
                $return[$templateType['id']] = $templateType['sys_template_type_description'];
            }
        }
        $datas = Html::dropDownList('template_type_id',0,$return, ['class' => 'form-control z-select mr10 mb5']);
        return json_encode(['status' => 1,'datas' => $datas]);
    }
  
    
} 